const connection = require('../app/database')
const userService = require('./user.service')

class ClubService {
	// 创建社团
	async create({ name, description, leader_id, club_avatar }) {
		try {
			// 开启一个事务
			// await connection.beginTransaction()
			const statement =
				'INSERT INTO clubs (name, description, leader_id, club_avatar) VALUES (?, ?, ?, ?)'
			const [result] = await connection.execute(statement, [
				name,
				description,
				leader_id,
				club_avatar
			])
			const clubId = result.insertId
			console.log(clubId)
			const [res] = await connection.execute(
				'INSERT INTO `club_members` (`club_id`, `user_id`, `role`,`status`) VALUES (?, ?, "leader","active")',
				[clubId, leader_id]
			)
			// 修改用户的角色
			if (leader_id !== 1) {
				await userService.editRoleLevel(leader_id, 1)
			}

			// 提交事务
			// await connection.commit()

			return res
		} catch (err) {
			// 如果有任何错误发生，回滚事务
			// await connection.rollback()
			throw err
		}
	}
	// 查询社团是否存在
	async findClubByName(name) {
		try {
			const query = `SELECT * FROM clubs WHERE name = ?`
			const [result] = await connection.execute(query, [name])
			return result
		} catch (err) {
			throw err
		}
	}
	// 查询单个社团同名字模糊查询
	async findClubByNameLike(name, limit = 10, offset = 0, status = 'approved') {
		try {
			const query = `
				SELECT
					c.id id,
					c.name name,
					c.description description,
					c.created_at created_at,
					c.updated_at updated_at,
					c.club_avatar club_avatar,
					u.name leaderName
			FROM
				clubs c
			LEFT JOIN 
				users u
			on 
				c.leader_id = u.id
			WHERE
				c.status = ?
			AND
			  c.name Like  ?
		 	limit ${limit} offset ${offset}`
			const countQuery = `SELECT count(*) as count FROM clubs WHERE name Like  ? `
			const [result] = await connection.execute(query, [status, `%${name}%`])
			const [count] = await connection.execute(countQuery, [
				status,
				`%${name}%`
			])
			return {
				totalCount: count[0].count,
				list: result
			}
		} catch (err) {
			throw err
		}
	}

	// 查询所有社团
	async listClubs(limit = 10, offset = 0, status = 'approved') {
		try {
			const statement = `
			SELECT
				c.id id,
				c.name name,
				c.description description,
				c.created_at created_at,
				c.updated_at updated_at,
				c.club_avatar club_avatar,
				c.status status,
				c.leader_id leader_id,
				u.name leaderName
			FROM
				clubs c
			LEFT JOIN 
				users u
			on 
				c.leader_id = u.id
			WHERE c.status = ?
			LIMIT ${limit} offset ${offset}`
			const countQuery = `SELECT count(*) as count FROM clubs c WHERE c.status = ?`

			const [result] = await connection.execute(statement, [status])
			const [count] = await connection.execute(countQuery, [status])

			return {
				totalCount: count[0].count,
				list: result
			}
		} catch (err) {
			throw err
		}
	}

	// 根据ID查询单个社团
	async findClubById(id) {
		try {
			const statement = `
				SELECT
				 c.id id,
				 c.name name,
				 c.description description,
				 c.status status,
				 c.leader_id leader_id,
				 c.created_at created_at,
				 c.club_avatar club_avatar,
				 u.name leaderName,
				 up.contact_info contact_info,
				 up.email email,
				 up.avatar_url avatar_url
				FROM 
			 		clubs c
				LEFT JOIN
					users u
				ON c.leader_id = u.id
				LEFT JOIN
					user_profiles up
				ON u.id = up.user_id
			 	WHERE
				  c.id = ?`
			const [result] = await connection.execute(statement, [id])
			return result.length ? result[0] : null
		} catch (err) {
			throw err
		}
	}

	// 删除社团
	async deleteClub(id) {
		try {
			const statement = 'DELETE FROM clubs WHERE id = ?'
			const [result] = await connection.execute(statement, [id])
			return result
		} catch (err) {
			throw err
		}
	}

	// 查询社团的名字是否和其他社团名字冲突
	async queryClubNameUnique(name, club_id) {
		const statement = `
		SELECT id FROM clubs
		WHERE name = ? AND (? IS NULL OR id != ?);
	`
		const [existingClubs] = await connection.execute(statement, [
			name,
			club_id,
			club_id
		])
		return existingClubs
	}

	// 更新社团信息领导
	async updateClub(id, { name, description, leader_id, club_avatar }) {
		try {
			const clubInfo = await this.findClubById(id)
			// 判断leader 有没有变更
			if (clubInfo.leader_id !== leader_id) {
				// 查询表中是否有这个用户
				const member = await this.queryClubMemberById(id, leader_id)
				// 如果没有 直接参加社团并且改为leader
				if (member.length === 0) {
					await this.joinClub(id, leader_id, 'leader', 'active')
				} else {
					await this.updateClubMemberRole(id, leader_id, 'leader')
					// 修改用户的角色
					if (leader_id !== 1) {
						await userService.editRoleLevel(leader_id, 1)
					}
				}
				await this.updateClubMemberRole(id, clubInfo.leader_id, 'member')
				if (clubInfo.leader_id !== 1) {
					await userService.editRoleLevel(clubInfo.leader_id, 0)
				}
			}
			const statement =
				'UPDATE clubs SET name = ?, description = ?, leader_id = ?, club_avatar = ? WHERE id = ?'
			const [result] = await connection.execute(statement, [
				name,
				description,
				leader_id,
				club_avatar,
				id
			])
			return result
		} catch (err) {
			throw err
		}
	}

	//通过id更新社团状态
	async updateClubStatus(club_id, status) {
		try {
			const statement = 'UPDATE clubs SET status = ? WHERE id = ?'
			const [result] = await connection.execute(statement, [status, club_id])
			return result
		} catch (err) {
			throw err
		}
	}

	// 更新成员状态
	async updateClubMemberRole(
		club_id,
		user_id,
		role = 'member',
		status = 'active'
	) {
		try {
			const statement = `UPDATE club_members SET role = ?, status = ? WHERE club_id = ? AND user_id = ?`
			const [result] = await connection.execute(statement, [
				role,
				status,
				club_id,
				user_id
			])
			return result
		} catch (err) {
			throw err
		}
	}

	// 查询是否在社团中
	async queryClubMemberById(club_id, user_id) {
		try {
			const statement = `SELECT * FROM club_members WHERE club_id = ? AND user_id = ? `
			const [result] = await connection.execute(statement, [club_id, user_id])
			return result
		} catch (err) {
			throw err
		}
	}

	// 根据社团ID和成员状态查询社团成员列表
	async getClubMembersByClubIdAndStatus(
		club_id,
		limit = 10,
		offset = 0,
		name,
		status = 'active'
	) {
		try {
			console.log(club_id, limit, offset, name, status)
			let nameSql = ''
			const sqlParms = [club_id, status]
			if (name) {
				nameSql = `AND u.name LIKE ?`
				sqlParms.push(`%${name}%`)
			}
			const statement = `
					SELECT cm.user_id, cm.role, cm.join_date, cm.status,u.name, up.nickname, up.avatar_url, up.bio
					FROM club_members AS cm
					LEFT JOIN user_profiles AS up ON cm.user_id = up.user_id
					LEFT JOIN users AS u ON u.id = up.user_id
					WHERE cm.club_id = ? AND cm.status = ? ${nameSql}
					LIMIT ${limit} OFFSET ${offset};
				`
			const totalStatement = `
				SELECT count(*) count
				FROM club_members AS cm
				JOIN user_profiles AS up ON cm.user_id = up.user_id
				JOIN users AS u ON u.id = up.user_id
				WHERE cm.club_id = ? AND cm.status = ? ${nameSql}
			`
			const [members] = await connection.execute(statement, sqlParms)
			const [count] = await connection.execute(totalStatement, sqlParms)
			console.log(members)
			return {
				totalCount: count[0].count,
				list: members
			}
		} catch (err) {
			throw err
		}
	}

	// 更改成员状态
	async updateMemberStatus(club_id, user_id, status = 'active') {
		try {
			const statement = `
        UPDATE club_members
        SET status = ?
        WHERE club_id = ? AND user_id = ?;
      `
			const [result] = await connection.execute(statement, [
				status,
				club_id,
				user_id
			])
			return result
		} catch (err) {
			throw err
		}
	}

	// 用户申请加入社团
	async joinClub(club_id, user_id, role = 'member', status = 'pending') {
		try {
			// 首先检查用户是否已经在社团中或已经申请过
			const checkStatement = `
        SELECT * FROM club_members WHERE club_id = ? AND user_id = ?;
      `
			const [existingMember] = await connection.execute(checkStatement, [
				club_id,
				user_id
			])
			if (existingMember.length > 0) {
				return { alreadyExists: true, member: existingMember[0] }
			}

			// 如果用户未在社团中，则添加申请
			const insertStatement = `
        INSERT INTO club_members (club_id, user_id, role, status)
        VALUES (?, ?, ? , ?);
      `
			const [result] = await connection.execute(insertStatement, [
				club_id,
				user_id,
				role,
				status
			])
			return { alreadyExists: false, result }
		} catch (err) {
			throw err
		}
	}

	// 查询所有的社团用户信息
	async getPendingClubMembers(club_id) {
		try {
			const statement = `
					SELECT cm.user_id, cm.join_date, cm.status, up.nickname, up.avatar_url
					FROM club_members AS cm
					JOIN user_profiles AS up ON cm.user_id = up.user_id
					WHERE cm.club_id = ? and cm.role != 'leader';
				`
			const [pendingMembers] = await connection.execute(statement, [club_id])
			return pendingMembers
		} catch (err) {
			throw err
		}
	}

	// 查询已审核的社团用户信息
	async getReviewedClubMembers(club_id) {
		try {
			const statement = `
					SELECT cm.user_id, cm.join_date, cm.status, up.nickname, up.avatar_url
					FROM club_members AS cm
					JOIN user_profiles AS up ON cm.user_id = up.user_id
					WHERE cm.club_id = ? AND cm.status IN ('active', 'rejected');
				`
			const [reviewedMembers] = await connection.execute(statement, [club_id])
			return reviewedMembers
		} catch (err) {
			throw err
		}
	}

	// 查询参加了的社团
	async queryClubsForUser(id) {
		const statement = `
		SELECT c.id, c.name, c.description, cm.join_date, cm.role
		FROM clubs AS c
		JOIN club_members AS cm ON c.id = cm.club_id
		WHERE cm.user_id = ?;
	`
		const [clubs] = await connection.execute(statement, [id])
		return clubs
	}

	// 删除社团团员
	async removeClubMemberById(club_id, user_id) {
		try {
			const statement = `DELETE FROM club_members WHERE club_id = ? AND user_id = ? `
			const [result] = await connection.execute(statement, [club_id, user_id])
			return result
		} catch (err) {
			throw err
		}
	}

	// 根据id查找社团
	async queryClubByUserId(user_id) {
		const statement = `
		SELECT
			c.id id,
			c.name name,
			c.description description,
			c.created_at created_at,
			c.updated_at updated_at,
			c.club_avatar club_avatar,
			c.status status,
			u.name leaderName
		FROM
			clubs c
		LEFT JOIN 
			users u
		on 
			c.leader_id = u.id
		WHERE c.leader_id = ?`
		const [result] = await connection.execute(statement, [user_id])
		return result
	}

	// queryStatusById
	async queryStatusById(user_id, club_id) {
		const statement = `
			SELECT * FROM club_members WHERE user_id = ? AND club_id = ?
		`
		const [result] = await connection.execute(statement, [user_id, club_id])
		return result
	}

	// 查询社团的参与人数
	async queryJoinCount() {
		const statement = `
		SELECT
			c.name,
			(SELECT COUNT(*) FROM club_members cb WHERE c.id = cb.club_id AND cb.status='active') as value
		FROM clubs c
		`
		const [result] = await connection.execute(statement)
		return result
	}
}

module.exports = new ClubService()
